package com.smart.community.region.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Excel模板工具类
 * 
 * @author Wu.Liang
 * @since 2025-01-30
 */
@Slf4j
@Component
public class ExcelTemplateUtils {

    /**
     * 生成社区模板Excel文件
     */
    public byte[] generateCommunityTemplate() throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            
            // 创建楼栋信息工作表
            createBuildingSheet(workbook);
            
            // 创建单元信息工作表
            createUnitSheet(workbook);
            
            // 创建房户信息工作表
            createHouseholdSheet(workbook);
            
            // 输出到字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    /**
     * 创建楼栋信息工作表
     */
    private void createBuildingSheet(Workbook workbook) {
        Sheet sheet = workbook.createSheet("楼栋信息");
        
        // 设置列宽
        sheet.setColumnWidth(0, 20 * 256); // 社区名称
        sheet.setColumnWidth(1, 15 * 256); // 楼栋编号
        sheet.setColumnWidth(2, 20 * 256); // 楼栋名称
        sheet.setColumnWidth(3, 10 * 256); // 楼层数
        sheet.setColumnWidth(4, 10 * 256); // 单元数量
        sheet.setColumnWidth(5, 10 * 256); // 每层户数
        sheet.setColumnWidth(6, 15 * 256); // 楼栋类型
        sheet.setColumnWidth(7, 12 * 256); // 建筑年代
        sheet.setColumnWidth(8, 30 * 256); // 备注
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"社区名称", "楼栋编号", "楼栋名称", "楼层数", "单元数量", "每层户数", "楼栋类型", "建筑年代", "备注"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 创建示例数据
        createBuildingExampleData(sheet, workbook);
        
        // 添加数据验证
        addBuildingDataValidation(sheet, workbook);
    }

    /**
     * 创建单元信息工作表
     */
    private void createUnitSheet(Workbook workbook) {
        Sheet sheet = workbook.createSheet("单元信息");
        
        // 设置列宽
        sheet.setColumnWidth(0, 20 * 256); // 社区名称
        sheet.setColumnWidth(1, 15 * 256); // 楼栋编号
        sheet.setColumnWidth(2, 10 * 256); // 单元编号
        sheet.setColumnWidth(3, 15 * 256); // 单元名称
        sheet.setColumnWidth(4, 10 * 256); // 楼层数
        sheet.setColumnWidth(5, 12 * 256); // 房间数量
        sheet.setColumnWidth(6, 8 * 256);  // 户数
        sheet.setColumnWidth(7, 12 * 256); // 是否有电梯
        sheet.setColumnWidth(8, 12 * 256); // 电梯数量
        sheet.setColumnWidth(9, 15 * 256); // 门禁类型
        sheet.setColumnWidth(10, 10 * 256); // 状态
        sheet.setColumnWidth(11, 30 * 256); // 备注
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"社区名称", "楼栋编号", "单元编号", "单元名称", "楼层数", "房间数量", "户数", "是否有电梯", "电梯数量", "门禁类型", "状态", "备注"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 创建示例数据
        createUnitExampleData(sheet, workbook);
        
        // 添加数据验证
        addUnitDataValidation(sheet, workbook);
    }

    /**
     * 创建房户信息工作表
     */
    private void createHouseholdSheet(Workbook workbook) {
        Sheet sheet = workbook.createSheet("房户信息");
        
        // 设置列宽
        sheet.setColumnWidth(0, 20 * 256); // 社区名称
        sheet.setColumnWidth(1, 15 * 256); // 楼栋编号
        sheet.setColumnWidth(2, 10 * 256); // 单元编号
        sheet.setColumnWidth(3, 20 * 256); // 房户编号
        sheet.setColumnWidth(4, 12 * 256); // 房间号
        sheet.setColumnWidth(5, 10 * 256); // 楼层号
        sheet.setColumnWidth(6, 12 * 256); // 面积
        sheet.setColumnWidth(7, 15 * 256); // 户型
        sheet.setColumnWidth(8, 10 * 256); // 朝向
        sheet.setColumnWidth(9, 12 * 256); // 装修状态
        sheet.setColumnWidth(10, 12 * 256); // 房屋类型
        sheet.setColumnWidth(11, 10 * 256); // 状态
        sheet.setColumnWidth(12, 30 * 256); // 备注
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"社区名称", "楼栋编号", "单元编号", "房户编号", "房间号", "楼层号", "面积", "户型", "朝向", "装修状态", "房屋类型", "状态", "备注"};
        
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 创建示例数据
        createHouseholdExampleData(sheet, workbook);
        
        // 添加数据验证
        addHouseholdDataValidation(sheet, workbook);
    }

    /**
     * 创建标题样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    /**
     * 创建楼栋示例数据
     */
    private void createBuildingExampleData(Sheet sheet, Workbook workbook) {
        CellStyle dataStyle = createDataStyle(workbook);
        
        // 示例数据
        Object[][] data = {
            {"阳光花园", "A1", "A1栋", 18, 2, 4, "住宅楼", 2020, "精装修"},
            {"阳光花园", "A2", "A2栋", 18, 2, 4, "住宅楼", 2020, "精装修"},
            {"阳光花园", "B1", "B1栋", 12, 1, 6, "住宅楼", 2021, "简装"}
        };
        
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                }
                cell.setCellStyle(dataStyle);
            }
        }
    }

    /**
     * 创建单元示例数据
     */
    private void createUnitExampleData(Sheet sheet, Workbook workbook) {
        CellStyle dataStyle = createDataStyle(workbook);
        
        // 示例数据
        Object[][] data = {
            {"阳光花园", "A1", 1, "1单元", 18, 72, 0, "是", 2, "密码", "启用", "标准单元"},
            {"阳光花园", "A1", 2, "2单元", 18, 72, 0, "是", 2, "密码", "启用", "标准单元"},
            {"阳光花园", "A2", 1, "1单元", 18, 72, 0, "是", 2, "密码", "启用", "标准单元"}
        };
        
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                }
                cell.setCellStyle(dataStyle);
            }
        }
    }

    /**
     * 创建房户示例数据
     */
    private void createHouseholdExampleData(Sheet sheet, Workbook workbook) {
        CellStyle dataStyle = createDataStyle(workbook);
        
        // 示例数据
        Object[][] data = {
            {"阳光花园", "A1", 1, "A1-1-101", "101", 1, 89.5, "两室一厅", "南", "精装", "住宅", "空置", "南北通透"},
            {"阳光花园", "A1", 1, "A1-1-102", "102", 1, 89.5, "两室一厅", "北", "精装", "住宅", "空置", "南北通透"},
            {"阳光花园", "A1", 1, "A1-1-201", "201", 2, 89.5, "两室一厅", "南", "精装", "住宅", "空置", "南北通透"}
        };
        
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                } else if (data[i][j] instanceof Double) {
                    cell.setCellValue((Double) data[i][j]);
                }
                cell.setCellStyle(dataStyle);
            }
        }
    }

    /**
     * 创建数据样式
     */
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    /**
     * 添加楼栋数据验证
     */
    private void addBuildingDataValidation(Sheet sheet, Workbook workbook) {
        // 添加楼层数验证：1-100之间的整数
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "100"
        );
        DataValidation validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 3, 3));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "楼层数必须是1-100之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("楼层数", "请输入1-100之间的整数");
        sheet.addValidationData(validation);
        
        // 添加单元数量验证：1-20之间的整数
        constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "20"
        );
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 4, 4));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "单元数量必须是1-20之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("单元数量", "请输入1-20之间的整数");
        sheet.addValidationData(validation);
        
        // 添加每层户数验证：1-20之间的整数
        constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "20"
        );
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 5, 5));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "每层户数必须是1-20之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("每层户数", "请输入1-20之间的整数");
        sheet.addValidationData(validation);
    }

    /**
     * 添加单元数据验证
     */
    private void addUnitDataValidation(Sheet sheet, Workbook workbook) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        
        // 添加单元编号验证：1-99之间的整数
        DataValidationConstraint constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "99"
        );
        DataValidation validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 2, 2));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "单元编号必须是1-99之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("单元编号", "请输入1-99之间的整数");
        sheet.addValidationData(validation);
        
        // 添加楼层数验证：1-100之间的整数
        constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "100"
        );
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 4, 4));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "楼层数必须是1-100之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("楼层数", "请输入1-100之间的整数");
        sheet.addValidationData(validation);
        
        // 添加房间数量验证：正整数
        constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.GREATER_THAN,
            "0", null
        );
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 5, 5));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "房间数量必须是正整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("房间数量", "请输入正整数");
        sheet.addValidationData(validation);
    }

    /**
     * 添加房户数据验证
     */
    private void addHouseholdDataValidation(Sheet sheet, Workbook workbook) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        
        // 添加楼层号验证：1-100之间的整数
        DataValidationConstraint constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "100"
        );
        DataValidation validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 5, 5));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "楼层号必须是1-100之间的整数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("楼层号", "请输入1-100之间的整数");
        sheet.addValidationData(validation);
        
        // 添加面积验证：1-1000之间的正数
        constraint = validationHelper.createNumericConstraint(
            DataValidationConstraint.ValidationType.DECIMAL,
            DataValidationConstraint.OperatorType.BETWEEN,
            "1", "1000"
        );
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 6, 6));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "面积必须是1-1000之间的正数");
        validation.setShowPromptBox(true);
        validation.createPromptBox("面积", "请输入1-1000之间的正数");
        sheet.addValidationData(validation);
        
        // 添加状态下拉选择
        String[] statusOptions = {"空置", "已售", "已租"};
        constraint = validationHelper.createExplicitListConstraint(statusOptions);
        validation = validationHelper.createValidation(constraint, new CellRangeAddressList(1, 1000, 11, 11));
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("输入错误", "请从下拉列表中选择状态");
        validation.setShowPromptBox(true);
        validation.createPromptBox("状态", "请选择房屋状态");
        sheet.addValidationData(validation);
    }

    /**
     * 解析Excel文件
     */
    public Map<String, List<Map<String, Object>>> parseExcelFile(MultipartFile file) throws IOException {
        Map<String, List<Map<String, Object>>> result = new HashMap<>();
        
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            
            // 解析楼栋信息
            result.put("buildings", parseBuildingSheet(workbook.getSheet("楼栋信息")));
            
            // 解析单元信息
            result.put("units", parseUnitSheet(workbook.getSheet("单元信息")));
            
            // 解析房户信息
            result.put("households", parseHouseholdSheet(workbook.getSheet("房户信息")));
        }
        
        return result;
    }

    /**
     * 解析楼栋工作表
     */
    private List<Map<String, Object>> parseBuildingSheet(Sheet sheet) {
        List<Map<String, Object>> data = new ArrayList<>();
        
        if (sheet == null) {
            return data;
        }
        
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            Map<String, Object> rowData = new HashMap<>();
            rowData.put("communityName", getCellValue(row.getCell(0)));
            rowData.put("buildingNo", getCellValue(row.getCell(1)));
            rowData.put("buildingName", getCellValue(row.getCell(2)));
            rowData.put("floorCount", getCellValue(row.getCell(3)));
            rowData.put("unitCount", getCellValue(row.getCell(4)));
            rowData.put("unitsPerFloor", getCellValue(row.getCell(5)));
            rowData.put("buildingType", getCellValue(row.getCell(6)));
            rowData.put("buildYear", getCellValue(row.getCell(7)));
            rowData.put("remark", getCellValue(row.getCell(8)));
            
            data.add(rowData);
        }
        
        return data;
    }

    /**
     * 解析单元工作表
     */
    private List<Map<String, Object>> parseUnitSheet(Sheet sheet) {
        List<Map<String, Object>> data = new ArrayList<>();
        
        if (sheet == null) {
            return data;
        }
        
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            Map<String, Object> rowData = new HashMap<>();
            rowData.put("communityName", getCellValue(row.getCell(0)));
            rowData.put("buildingNo", getCellValue(row.getCell(1)));
            rowData.put("unitNo", getCellValue(row.getCell(2)));
            rowData.put("unitName", getCellValue(row.getCell(3)));
            rowData.put("floorCount", getCellValue(row.getCell(4)));
            rowData.put("roomCount", getCellValue(row.getCell(5)));
            rowData.put("householdCount", getCellValue(row.getCell(6)));
            rowData.put("hasElevator", getCellValue(row.getCell(7)));
            rowData.put("elevatorCount", getCellValue(row.getCell(8)));
            rowData.put("doorAccessType", getCellValue(row.getCell(9)));
            rowData.put("status", getCellValue(row.getCell(10)));
            rowData.put("remark", getCellValue(row.getCell(11)));
            
            data.add(rowData);
        }
        
        return data;
    }

    /**
     * 解析房户工作表
     */
    private List<Map<String, Object>> parseHouseholdSheet(Sheet sheet) {
        List<Map<String, Object>> data = new ArrayList<>();
        
        if (sheet == null) {
            return data;
        }
        
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            Map<String, Object> rowData = new HashMap<>();
            rowData.put("communityName", getCellValue(row.getCell(0)));
            rowData.put("buildingNo", getCellValue(row.getCell(1)));
            rowData.put("unitNo", getCellValue(row.getCell(2)));
            rowData.put("householdCode", getCellValue(row.getCell(3)));
            rowData.put("roomNumber", getCellValue(row.getCell(4)));
            rowData.put("floorNumber", getCellValue(row.getCell(5)));
            rowData.put("area", getCellValue(row.getCell(6)));
            rowData.put("houseType", getCellValue(row.getCell(7)));
            rowData.put("orientation", getCellValue(row.getCell(8)));
            rowData.put("decorationStatus", getCellValue(row.getCell(9)));
            rowData.put("houseCategory", getCellValue(row.getCell(10)));
            rowData.put("status", getCellValue(row.getCell(11)));
            rowData.put("remark", getCellValue(row.getCell(12)));
            
            data.add(rowData);
        }
        
        return data;
    }

    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == (int) value) {
                        return (int) value;
                    } else {
                        return value;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}
